package com.netx.cubigraf.apps.utilities;
import java.io.PrintStream;
import com.netx.generics.sql.BasicDataSource;
import com.netx.generics.sql.Table;
import com.netx.generics.time.Date;
import com.netx.generics.util.Strings;
import java.sql.*;
import java.text.SimpleDateFormat;


public class PRJ09_DatabaseMigrator {

	public static void run() throws Throwable {
		PRJ09_DatabaseMigrator migrator = new PRJ09_DatabaseMigrator();
		migrator.migrate();
	}
	
	private final PrintStream _out = System.out;
	
	public void migrate() throws Throwable {
		// Old database:
		BasicDataSource dsOld = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/xnet_cubigraf?autoReconnect=true", "root", "cubigraf");
		Connection cOld = dsOld.getConnection();
		Statement stmtOld = cOld.createStatement();
		// New database:
		BasicDataSource dsNew = new BasicDataSource("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/cubigraf?autoReconnect=true", "root", "cubigraf");
		Connection cNew = dsNew.getConnection();
		Statement stmtNew = cNew.createStatement();

		// precos:
		_out.print("migrating table \"precos\"... ");
		stmtNew.executeUpdate("DELETE FROM precos");
		Table precos = new Table(stmtOld.executeQuery("SELECT * FROM precos"));
		for(int i=0; i<precos.getRowCount(); i++) {
			String query = "INSERT INTO precos(cdg_preco, preco) VALUES ("
				+precos.getString("codigo", i)+", "
				+precos.getString("preco", i)+")"
			;
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(precos.getRowCount()+" lines updated");
		
		// clientes
		_out.print("migrating table \"clientes\"... ");
		stmtNew.executeUpdate("DELETE FROM clientes");
		Table clientes = new Table(stmtOld.executeQuery("SELECT * FROM clientes"));
		for(int i=0; i<clientes.getRowCount(); i++) {
			String codigo_infologia = clientes.getString("codigo_infologia", i);
			codigo_infologia = codigo_infologia.equals("0") ? "NULL" : codigo_infologia;
			String query = "INSERT INTO clientes(cdg_cliente, nome, nome_completo, active, cdg_infologia) VALUES ("
				+clientes.getString("codigo", i)+", "
				+"\""+Strings.addSlashes(clientes.getString("nome", i))+"\", "
				+"\""+Strings.addSlashes(clientes.getString("nome_completo", i))+"\", "
				+"1, "
				+codigo_infologia+")"
			;
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(clientes.getRowCount()+" lines updated");
		
		// facturas
		_out.print("migrating table \"facturas\"... ");
		stmtNew.executeUpdate("DELETE FROM facturas");
		Table facturas = new Table(stmtOld.executeQuery("SELECT * FROM facturas"));
		for(int i=0; i<facturas.getRowCount(); i++) {
			String query = "INSERT INTO facturas(cdg_factura, numero, cdg_cliente, estado, data_criacao) VALUES ("
				+facturas.getString("codigo", i)+", "
				+facturas.getString("numero", i)+", "
				+facturas.getString("codigo_cliente", i)+", "
				+"\""+facturas.getString("estado", i)+"\", "
				+"\""+facturas.getString("data_criacao", i)+"\")"
			;
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(facturas.getRowCount()+" lines updated");
		
		// servicos
		_out.print("migrating table \"servicos\"... ");
		stmtNew.executeUpdate("DELETE FROM servicos");
		Table servicos = new Table(stmtOld.executeQuery("SELECT * FROM servicos"));
		for(int i=0; i<servicos.getRowCount(); i++) {
			String query = "INSERT into servicos(cdg_servico, referencia, nome, estado, data_abertura, data_print, tipo, cdg_cliente, cdg_desenhador, cdg_original, numero_cores, branco_highlight, base, largura, altura, numero_linhas, camada, cdg_unidade_saida, tamanho, alteracao_tamanho) VALUES ("
				+servicos.getString("codigo", i)+", "
				+servicos.getString("referencia", i)+", "
				+"\""+Strings.addSlashes(servicos.getString("nome", i))+"\", "
				+"\""+servicos.getString("estado", i)+"\", "
				+"\""+servicos.getString("data_abertura", i)+"\", "
				+"\""+servicos.getString("data_print", i)+"\", "
				+"\""+servicos.getString("tipo", i)+"\", "
				+servicos.getString("codigo_cliente", i)+", "
				+servicos.getString("codigo_desenhador", i)+", "
				+servicos.getString("codigo_original", i)+", "
				+servicos.getString("numero_cores", i)+", "
				+servicos.getString("branco_highlight", i)+", "
				+servicos.getString("base", i)+", "
				+servicos.getString("largura", i)+", "
				+servicos.getString("altura", i)+", "
				+servicos.getString("numero_linhas", i)+", "
				+"\""+servicos.getString("camada", i)+"\", "
				+servicos.getString("codigo_unidade_saida", i)+", "
				+"\""+servicos.getString("tamanho", i)+"\", "
				+servicos.getString("alteracao_tamanho", i)+")"
			;
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(servicos.getRowCount()+" lines updated");

		// linhas_factura e nao_facturados
		_out.print("creating tables \"linhas_factura\" / \"nao_facturados\"... ");
		stmtNew.executeUpdate("DELETE FROM linhas_factura");
		stmtNew.executeUpdate("DELETE FROM nao_facturados");
		Table servicosFacturados = new Table(stmtOld.executeQuery("SELECT * FROM servicos WHERE estado=\"p\" OR estado=\"f\""));
		int enviados = 0, naoEnviados = 0;
		for(int i=0; i<servicosFacturados.getRowCount(); i++) {
			String query = null;
			if(servicosFacturados.getInt("facturacao", i) == 1) {
				// enviados para facturacao:
				String cod_factura = _verFacturaPara(stmtOld, servicosFacturados.getString("data_aprecado", i), servicosFacturados.getString("codigo_cliente", i));
				query = "INSERT INTO linhas_factura(cdg_factura, cdg_servico, preco, passado, data_aprecado) VALUES ("
					+cod_factura+", "
					+servicosFacturados.getString("codigo", i)+", "
					+servicosFacturados.getString("preco", i)+", "
					+"0, "
					+"\""+servicosFacturados.getString("data_aprecado", i)+"\")"
				;
				enviados++;
			}
			else {
				// nao enviados:
				query = "INSERT INTO nao_facturados(cdg_servico, preco, data_aprecado) VALUES ("
					+servicosFacturados.getString("codigo", i)+", "
					+servicosFacturados.getString("preco", i)+", "
					+"\""+servicosFacturados.getString("data_aprecado", i)+"\")"
				;
				naoEnviados++;
			}
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(enviados+" / "+naoEnviados+" lines created");

		_out.print("updating table \"linhas_factura\", passado=true... ");
		Table passados = new Table(stmtOld.executeQuery("SELECT codigo_servico FROM linhas_factura WHERE passado=1"));
		for(int i=0; i<passados.getRowCount(); i++) {
			String query = "UPDATE linhas_factura SET passado=1 WHERE cdg_servico="+passados.getString(1, i);
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(passados.getRowCount()+" lines updated");
		
		// gestao_miniaturas:
		_out.print("migrating table \"gestao_miniaturas\"... ");
		stmtNew.executeUpdate("DELETE FROM gestao_miniaturas");
		Table miniaturas = new Table(stmtOld.executeQuery("SELECT * FROM gestao_miniaturas"));
		for(int i=0; i<miniaturas.getRowCount(); i++) {
			String query = "INSERT INTO gestao_miniaturas(cdg_cliente, data) VALUES ("
				+miniaturas.getString(1, i)+", "
				+"\""+miniaturas.getString(2, i)+"\")"
			;
			try {
				stmtNew.executeUpdate(query);
			}
			catch(SQLException sqle) {
				_out.println(query);
				throw sqle;
			}
		}
		_out.println(miniaturas.getRowCount()+" lines updated");
		
		// eliminar fichas de servico:
		stmtNew.executeUpdate("DELETE FROM fichas_entrega");
		stmtNew.executeUpdate("DELETE FROM linhas_ficha_entrega");
		

		stmtOld.close();
		cOld.close();
		stmtNew.close();
		cNew.close();
		_out.println("migration completed successfully.");
	}
	
	private static String _verFacturaPara(Statement stmt, String data_aprecado, String codCliente) throws SQLException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = Date.valueOf(data_aprecado, sdf);
		date.setDay(date.getDay()<16 ? 1 : 16);
		ResultSet rs = stmt.executeQuery("SELECT codigo FROM facturas WHERE data_criacao=\""+date.format(sdf)+"\" AND codigo_cliente="+codCliente);
		rs.next();
		return rs.getString(1);
	}
}
